Two new commands added: SQL-appendRows, SQL-transact
One bugfix : older versions of the plugin were preventing FileMaker Pro to start up properly when the FileMaker Pro application was launched using AppleScript.
New features in version 1.2 rev. B
Support for Euro currency symbol.
There was one change between FMP 4.0v1 and FMP 4.0v2/4.1 to support the new Euro symbol. Mac 219 used to map to Windows 164, this was the currency (circle with four lines coming out of it) symbol in both character sets. Mac 173 used to map to Windows 128, this was an unused position on Windows, and the "not equal" symbol on Mac. When Apple decided to change the old currency symbol to be the new Euro symbol we had to change our tables. Now in 4.0v2 and 4.1 Mac 219 maps to Windows 128 (Euro symbol
on both) and Mac 173 (not equal) maps to Windows 164 (currency symbol).
New features in version 1.2 rev. A
SQL-tables, "table_type_identifier" - Table type identifier; one of the following:
Explanation of commands exportBlob (binary large object) and importBlob.
Command exportBlob exports from the SQL database through the plugin to the disk. In other words the plugin writes/exports
a blob to the disk.
Command importBlob imports from the disk through the plugin into the SQL database.
In other words the plugin reads/imports a blob from the disk.
Changes to previous versions
•MacOS 8.5 compatibility modifications.
•Time spent on network scanning is reduced to minimum (the plugin starts up faster).
•Connect/disconnect procedure is modified to prevent some errors occuring with some ODBC drivers.
•Performance update.
Command SQL-directColumn has been extended to all columns specified in the query. Retrieved columns are separated with | (pipeline).
Important note for the users that are using this command.
If your query contains more than ONE column please adjust your query if you don't want to retrieve
all specified columns.
For example in version 1.1 SQL-directColumn,"Select column1,column2 where column3=something" returns only column1. In version 1.2 same command returns column1 and column2 as column1|column2 .
If optional parameter "|R" (pipline R) is added then the command returns all rows in the found set. Rows are separated with LF and maximal result size is 64K.
Installation:
Drag the plug-in into Filemaker extensions folder.
Install third party ODBC software.
Configure your ODBC settings and data source's properly before using.
Do not try to run/use SQL Plugin without having ODBC software installed!!!
It will cause the Filemaker application to crash.
You can obtain/buy ODBC software from Internet :
http://www.openlinksw.com
http://www.augsoft.com
http://www.intersolv.com
Registration:
You can register plug-in using "SQL-Register" command (see plugin commands) or through plug-in's preferences. Registration data will be provided to you as soon your payment is confirmed. In the mean time you may use the unregistered plug-in with one user license. The plug-in will 'expire' after one hour and then you have to restart Filemaker Pro if you want to use the plug-in again. However the plug-in does not force you to restart Filemaker Pro.
Plugin commands:
SQL-version
Syntax: SQL-version , ""
Returns the current version number
SQL-open
Syntax: SQL-open , "fieldname"
fieldname contains datasource, login, password separated with |
returns: "OK" if it works, otherwise, if reporting is enabled, it returns full odbc/sql error string.
Used to open a datasource, once opened a datasource remains opened until Filemaker is closed or command SQL-close is used.
Opening another datasource will close the current datasource.
SQL-doSQL
Syntax: SQL-doSQL , "fieldname"
fieldname contains an SQL command to create, insert, update, delete a table etcetera.
use standard SQL statements
returns: "OK" if correct. In case of an error if reporting is enabled it returns full odbc/sql error string, otherwise "EXCEPTION".
SQL-execQuery
Syntax: SQL-execQuery , "fieldname "
fieldname contains an SQL query with standard SQL statements
returns: "Columns in result:x" if correct (where is x =number of columns in row) otherwise
in case of an error if reporting is enabled it returns full odbc/sql error string, if reporting is not enabled "EXCEPTION".
SQL-getRow
Syntax SQL-getRow , ""
"" because no parameters are needed. Row at current cursor position is set into referenced field.
returns: data if any found , "EMPTY" if no data found, in case of an error if reporting is enabled returns full odbc/sql error string otherwise "EXCEPTION"
All fields from one row come into one field separated with delimiter. Standard delimiter is "|" (pipeline) but it can be changed. See SQL-setDelimiter.
Use Filemaker calculations and/or scripts to split into different fields.
Maximal column width is 64K bytes, row size 64K.
SQL-getColumn
Syntax SQL-getColumn,"" or SQL-getColumn,"1"
If no parameter is given command returns "EMPTY" only if no more data is found , with parameter "1" it returns "EMPTY" on the end of each row.
returns: one column at the time if any found, in case of an error if reporting is enabled it returns full odbc/sql error string otherwise "EXCEPTION"
Maximal column width is 64K bytes, row size not limited.
returns: all columns of first row specified in the query ,if any rows were found. In case of an error if reporting is enabled it returns full odbc/sql error string otherwise "EXCEPTION"
Maximal width of all columns together is 64K bytes.
If optional parameter "|R" (pipeline R) is added the command retrurs all rows in the found set separated with LF. Maximal result size is 64K. If result contains more data then the data is truncated and the last returned row contains word "EXCEPTION".
SQL-call
Syntax SQL-call , "procedure name"
call a stored procedure, results can be retrieved by getRow
returns: "OK" if correct otherwise if reporting is enabled it returns full odbc/sql error string
SQL-enableReporting
Syntax SQL-enableReporting , "1" for enabling , SQL-enableReporting , "0" for disabling.
returns: "OK"
SQL-setDelimiter
Syntax SQL-setDelimiter , "delimiter"
set delimiter for retrieved columns in a row.
Each time the plugin is initialized it will use standard "|" (pipeline) delimiter.
returns: "OK"
SQL-getDelimiter
Syntax SQL-setDelimiter , ""
returns: current delimiter (not necessary the standard one)
SQL-register
Syntax SQL-register , "User|Key|Licenses"
Example : SQL-Register , "RAA|123456|10"
returns: "OK" if registration is succesful otherwise "NOT REGISTERED"
SQL-exportRows
Syntax SQL-exportRows , "path:filename"
Creates a TAB-separated text file from an SQL query starting at current row. Select statement (query) needs to be placed before executing this command. If you want to import created file into FileMaker Pro database, use FMP script step "Import Records". For proper import specify that the file is tab-separated text.
Example : SQL-exportRows , "Macintosh HD:Export files:MySQLExport1"
Specifying only a filename without full access path will create a file in FileMaker Pro folder.
Executing without filename will create file "SQLPluginExport" in FileMaker Pro folder that will be deleted
when you quit Filemaker.
Maximal column width is 64000 bytes, row size not limited.
returns: "OK" if correct otherwise if reporting is enabled it returns full error string
SQL-close
Syntax SQL-close , ""
This command disconnects you from a remote RDBMS. There is no need anymore to close Filemaker to get disconnected. Some memory allocated to the plugin will be released.
returns: "OK"
SQL-deleteFile
Syntax SQL-deleteFile , "path:filename"
Example : SQL-deleteFile , "Macintosh HD:Export files:MySQLExport1"
This command will delete the specified file.
Specifying only a filename without full access path will attempt to delete specified file in FileMaker Pro folder. There are no warnings whatsoever, so be careful with specifying files to delete.
returns: "OK" if correct otherwise if reporting is enabled it returns full error string
New features in ODBC/SQL plugin for Filemaker 4.x version 1.2 beta
SQL-executeFile
Syntax SQL-executeFile,"filename|1 (optional)"
returns: "OK" if correct otherwise if reporting is enabled it returns full odbc/sql error string
This command executes SQL commands from specified text file. If parameter (1) specified file will be executed line by line and not as one big statement.
SQL commands should end with (be separated by) semicolon (;).
For example :
Create table test ( I Integer, T char(3));
Insert into test values (1,'ABC');
Insert into test values (2,'DEF');
Executing this file will create table test on the SQL server and insert specified values into that table.
File will be sent to the ODBC driver as ONE big SQL statement.
If the file is too big to fit into memory or the ODBC driver will not accept it because its size, then you can set optional parameter to 1. Doing that the plugin will execute file line by line (line is an SQL statement with semicolon ( ; ) on the end of it.) In the example above the plugin will execute :
Create table test ( I Integer, T char(3)); -as first SQL statement
Insert into test values (1,'ABC'); -as second SQL statement
Insert into test values (2,'DEF'); -as third SQL statement
In this case it would not make any difference but in situation where you are defining some stored procedures or using complex nested SQL statements, file can NOT be executed line by line.
For example following file can NOT be executed line by line.
Create procedure getinfo(artist_nr)
returns varchar, varchar, float;
argument char artist_nr;
{
SELECT artwork_number,title,price FROM artworks WHERE artist_number = artist_nr;
fetch;
return artwork_number, title, price;
}
end procedure getinfo;
If you are about to insert large amount of rows into an SQL table, try to do that in a separate file executing it line by line and do not combine it with definitions of stored procedures or multiline statements that should be executed at once. Executing file line by line has very little negative influence on performance.
SQL-exportBlob
Syntax SQL-exportBlob,"filename|type|creator"
returns: "OK" if correct otherwise if reporting is enabled it returns full odbc/sql error string
This command is similar to SQL-exportRows but it exports only the first column of the first row in binary (raw) format. This command is supposed to be used after making a query first.
Our intention is to make a workaround for not being able to write into container fields from the plugin.
Users can query an binary column from SQL server, save that as file and the tell FMP to import that file
into a container. Binary column can contain pictures, sounds, movies that can be imported into FMP.
where id, image1, image2 and image3 are column names in table called images and img1.jpg, img2.jpg, img3.jpg are filenames of images stored on the disk.
Specified statement and filenames should be separated with | (pipeline) and number of input parameters ( ? questions marks) must be equal to number of filenames.
However, this command is not restricted just for image files, you can use is to insert any type of binary data like movies, sounds, text files etc.
Suppose that you want to update images stored in columns image2 and image3 where id is one, then the statament would be :
SQL-importBlob,("Update images set image2=?, image3=? where id=1|img2new.jpg|img3new.jpg")
where img2new.jpg and img3new.jpg are filenames of new versions of images img2.jpg and img3.jpg .
Images, movies, sounds, documents etc. can only be imported in binary columns or RDBMS specific types
of columns that can contain binary data.
SQL-convert
Syntax SQL-convert, "", "ANSI" or "OEM"
returns: "OK"
We discovered that FMP internal uses Mac character set on both MacOS and Windows for those languages that don't use multibyte char's. This leads to unreadable text when data is inserted into SQL table with a program different from FMP. For example if MS Access inserts text containing characters with ASCII values bigger than 127 and FMP retrieves those data then those characters are not displayed properly.
Other way around we have the same problem.
This command will enable character set conversion from Mac to ANSI and Mac to OEM and vice versa from ANSI to Mac and OEM to Mac.
Parameter "ANSI" enables Mac To ANSI and ANSI To Mac
Parameter "OEM" enables Mac To OEM and OEM To Mac
No parameter ("") disables conversion
Default there is no conversion.
This command is not meant to be used on OS's that are using use multibyte char's such as
Japanese OS's. From documentation available to us it apperars that all programs on those platforms
can use the same character set.
SQL-binary
SQL-binary , "0 "or "1"
returns: "OK"
This command enables/disables support for binary columns. Maybe you already noticed in previous versions that if retrieving a binary column with getRow, getColumn, directColumn or exportRows all data from binary column is converted to hexadecimal values.
If user enables binary support then data will not be converted to hex values but it will be retrieved in the binary raw format resulting in showing all bytes in character/text form.
Suppose that binary column contains a picture. Retrieving that column with binary support off (same thing happens in previous versions) will result in FMP displaying something like 0005HE45DFEDA3A7H6 etc.
If binary support is on FMP will display something like: åaU éç 2 4 .
If the column contains readable text then with binary support on this text should properly be displayed in FMP and with binary support off FMP will display hex values of text characters.
SQL-appendRows
Syntax SQL-appendRows,"filename1|filename2"
returns: "OK" if correct otherwise if reporting is enabled it returns full odbc/sql error string
This command executes SQL commands from specified text file (filename1) and returns all results in tab separated text file (filename2).
SQL commands in the "input file" should end with (be separated by) semicolon (;).
This command combines functionality of SQL-executeFile and SQL-exportRows.
SQL-transact
Syntax SQL-commit,"parameter"
The parameter can be one of the following:
AUTOCOMMIT_ON
AUTOCOMMIT_OFF
COMMIT
ROLLBACK
Returns "OK".
AUTOCOMMIT_ON will force SQL server to commit all changes/updates as you make them (rollback is not possible). Default plugin "mode" is AUTOCOMMIT_ON.
AUTOCOMMIT_OFF will give you possibility to rollback or commit your transactions as you wish.
All running transaction will be committed if you quit FMP, disconnect/reconnect from datasource or connect to another datasource. If you don't wont to commit your changes execute first an rollback and then quit.
COMMIT will force SQL server to commit all changes/updates that were made.
ROLLBACK will force SQL server to reverse all changes/updates that were made up to the point where last commit command was issued or if not commit was performed it will return the database in the same state as when you connected. This parameter can only be used if AUTOCOMMIT_OFF was used first.
Known problems/issues
Using plugin without having ODBC software (driver manager) installed will cause Filemaker to crash when starting up application.
Apple Shared Library Manager (ASLM) attempts to load ODBC driver manager even before the plugin enters its code so there is no chance to disable the plugin. If there is no ODBC driver manager installed ASLM will generate an error alert that causes Filemaker to crash. Unfortunately, this problem cannot be fixed from within the plugin. No updates are planned regarding this issue.
Using the plugin without having enough memory assigned to Filemaker (less than 5000 Kb) will cause Filemaker to crash when starting up application.
Apple Shared Library Manager (ASLM) attempts to load ODBC driver manager even before the plugin enters its code so there is no chance to disable the plugin. If there is not enough memory the ODBC driver manager will generate an error that causes Filemaker to crash. Unfortunately, this problem cannot be fixed from within the plugin. No updates are planned regarding this issue.
When inserting data into some RDBMS the plugin does not get any errors if the data are truncated so we cannot pass them on. This problem is either driver related or RDBMS related; sometimes either of them does report this error. We are still working on this. In the meantime be sure that you do not send a larger amount of data than the size of your columns.
Some RDBMS can limit the size of data types that are supported. For example data type VARCHAR can be
limited to 32000 characters. If you create a table with a VARCHAR(64000) you might not get any error. You can end up in a number of situations: RDBMS creates a column that is 32000 characters wide and inserting 64000 characters might or might not give an error. In both cases we experienced that data WAS truncated. What can also happen is that RDBMS creates a column that is 2Gb bytes wide and more than 32000 characters can fit in. We are still working on this. In the meantime be careful.
Some ODBC drivers will report an error (usually a function sequence error) when working with columns of type VARCHAR and/or CHAR and smart quotes are turned on. To prevent this from happening, you should turn off smart quotes in your Document Preferences.
All comments are welcome.
MacOS, Windows, ODBC, SQL, Filemaker Pro are trademarks registered by their
respective owners.
Any questions and suggestions can be sent to us at bane@xs4all.nl
